package cn.lwq.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import cn.lwq.help.ConfigRead;
import cn.lwq.help.Make;

/**
 * @auther lwq 719812998@qq.com
 * @date 2017年7月17日
 * @comment 
 */
public class MakeMysql extends Make {

	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	private String sql = "SELECT COLUMN_NAME, COLUMN_COMMENT," +
	"DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION " +
	"FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='%s' " +
	"AND TABLE_SCHEMA='%s' ORDER BY ORDINAL_POSITION ASC";
	
	private String COLUMN_NAME;//字段名
	private String COLUMN_COMMENT;//字段备注
	private String DATA_TYPE;//字段类型
	private String CHARACTER_MAXIMUM_LENGTH;//字符型字段最大长度
	private String NUMERIC_PRECISION;//浮点型字段最大长度
	
	
	@Override
	public StringBuilder makeJavaField() throws Exception {
		StringBuilder sbStr = new StringBuilder();
		conn = getConnection();
		ps = conn.prepareStatement(String.format(sql, ConfigRead.TABLE_NAME, ConfigRead.DATABASE_DBNAME));
		rs = ps.executeQuery();
		int counts = 0;
		while(rs.next()) {
			COLUMN_NAME = rs.getString("COLUMN_NAME");
			COLUMN_COMMENT = rs.getString("COLUMN_COMMENT");
			DATA_TYPE = rs.getString("DATA_TYPE").toLowerCase();
			CHARACTER_MAXIMUM_LENGTH = rs.getString("CHARACTER_MAXIMUM_LENGTH");
			NUMERIC_PRECISION = rs.getString("NUMERIC_PRECISION");
			sbStr.append(javaFieldCheck(COLUMN_NAME, COLUMN_COMMENT, DATA_TYPE, 
					CHARACTER_MAXIMUM_LENGTH == null ? NUMERIC_PRECISION : CHARACTER_MAXIMUM_LENGTH));
			counts ++;
		}
		sbStr.append("//共["+counts+"]个字段");
		close(ps, rs);
		return sbStr;
	}

	@Override
	public StringBuilder makeSqlField() throws Exception {
		StringBuilder sbStr = new StringBuilder();
		conn = getConnection();
		ps = conn.prepareStatement(String.format(sql, ConfigRead.TABLE_NAME, ConfigRead.DATABASE_DBNAME));
		rs = ps.executeQuery();
		while(rs.next()) {
			COLUMN_NAME = rs.getString("COLUMN_NAME");
			if(ConfigRead.HUMP) {
				sbStr.append(COLUMN_NAME + " as "+ turnHump(COLUMN_NAME) +",");
			} else {
				sbStr.append(COLUMN_NAME + ",");
			}
			sbStr.append(ConfigRead.NL);
		}
		close(ps, rs);
		sbStr.delete(sbStr.lastIndexOf(","), sbStr.length());
		return sbStr;
	}

	@Override
	public StringBuilder makeMybatisSet() throws Exception {
		StringBuilder sbStr = new StringBuilder();
		conn = getConnection();
		ps = conn.prepareStatement(String.format(sql, ConfigRead.TABLE_NAME, ConfigRead.DATABASE_DBNAME));
		rs = ps.executeQuery();
		while(rs.next()) {
			COLUMN_NAME = rs.getString("COLUMN_NAME");
			DATA_TYPE = rs.getString("DATA_TYPE").toLowerCase();
			DATA_TYPE = truncStr(DATA_TYPE);
			if(ConfigRead.HUMP) {
				sbStr.append(String.format(ConfigRead.FORMAT_MYBATIS_SET, COLUMN_NAME, turnHump(COLUMN_NAME), DATA_TYPE));
			} else {
				sbStr.append(String.format(ConfigRead.FORMAT_MYBATIS_SET, COLUMN_NAME, COLUMN_NAME, DATA_TYPE));
			}
			sbStr.append(ConfigRead.NL);
		}
		close(ps, rs);
		sbStr.delete(sbStr.lastIndexOf(","), sbStr.length());
		return sbStr;
	}

	@Override
	public StringBuilder makeMybatisInsert() throws Exception {
		StringBuilder sbStr = new StringBuilder();
		conn = getConnection();
		ps = conn.prepareStatement(String.format(sql, ConfigRead.TABLE_NAME, ConfigRead.DATABASE_DBNAME));
		rs = ps.executeQuery();
		while(rs.next()) {
			COLUMN_NAME = rs.getString("COLUMN_NAME");
			DATA_TYPE = rs.getString("DATA_TYPE");
			DATA_TYPE = truncStr(DATA_TYPE);
			if(ConfigRead.HUMP) {
				sbStr.append(String.format(ConfigRead.FORMAT_MYBATIS_INSERT, turnHump(COLUMN_NAME), DATA_TYPE));
			} else {
				sbStr.append(String.format(ConfigRead.FORMAT_MYBATIS_INSERT, COLUMN_NAME, DATA_TYPE));
			}
			sbStr.append(ConfigRead.NL);
		}
		close(ps, rs);
		sbStr.delete(sbStr.lastIndexOf(","), sbStr.length());
		return sbStr;
	}
	
	@Override
	public StringBuilder makeMybatisUpdate() throws Exception {
		StringBuilder sbStr = new StringBuilder();
		conn = getConnection();
		ps = conn.prepareStatement(String.format(sql, ConfigRead.TABLE_NAME, ConfigRead.DATABASE_DBNAME));
		rs = ps.executeQuery();
		sbStr.append("<set>").append(ConfigRead.NL);
		while(rs.next()) {
			COLUMN_NAME = rs.getString("COLUMN_NAME");
			DATA_TYPE = rs.getString("DATA_TYPE");
			DATA_TYPE = truncStr(DATA_TYPE);
			if(ConfigRead.HUMP) {
				sbStr.append(String.format(ConfigRead.FORMAT_MYBATIS_UPDATE,
						turnHump(COLUMN_NAME) + " != null", COLUMN_NAME,
						turnHump(COLUMN_NAME), DATA_TYPE));
			} else {
				sbStr.append(String.format(ConfigRead.FORMAT_MYBATIS_UPDATE,
						COLUMN_NAME + " != null", COLUMN_NAME,
						COLUMN_NAME, DATA_TYPE));
			}
			sbStr.append(ConfigRead.NL);
		}
		close(ps, rs);
		sbStr.append("</set>");
		return sbStr;
	}
	
	private String truncStr(String DATA_TYPE) {
		switch (DATA_TYPE) {
			case "text" : 
			case "tinytext" :
			case "mediumtext" :
			case "longtext" :
				DATA_TYPE = "VARCHAR"; break;
			case "int" : 
			case "tinyint" :
			case "smallint" :
			case "mediumint" :
				DATA_TYPE = "INTEGER"; break;
			case "mediumblob" :
			case "longblob" :
				DATA_TYPE = "BLOB"; break;
			case "datetime" : DATA_TYPE = "TIMESTAMP"; break;
			default : DATA_TYPE = DATA_TYPE.toUpperCase(); break;
		}
		return DATA_TYPE;
	}


}
